﻿using System;
using System.Collections.Generic;
using System.Data.Common;
using System.Data;
using System.Text;
using Sinacor.Infra.Service.Data;
using Sinacor.Infra.Service.Security.Authorization;

namespace Sinacor.Servico.Bovespa.Contabil.ResourceAccess
{
    /// <summary>
    /// ResourceAccess Class - ContaContabil
    /// </summary>
    public class ContaContabil : BaseResourceAccess
    {
        #region Construtor

        /// <summary>
        /// Construtor
        /// </summary>
        public ContaContabil() : base(AuthorizationContext.Current.CompanyId) { }

        #endregion

        #region Métodos Públicos

        /// <summary>
        /// Consultar contas contábeis
        /// </summary>
        /// <param name="parameter">BusinessEntity.Parameter.FiltroContaContabil</param>
        /// <returns>Lista de entidades de negócio do tipo BusinessEntity.ContaContabil</returns>
        public List<BusinessEntity.ContaContabil> ConsultarContasContabeis(BusinessEntity.Parameter.FiltroContaContabil parameter)
        {
            #region Comando SQL

            bool jaExisteFiltroInformado = false;

            StringBuilder sql = new StringBuilder();

            sql.Append("SELECT COD_RESU_CON, ");
            sql.Append("DIG_CON, COD_CCTB_FORT, NOME_CON ");
            sql.Append("FROM TCTCAD_PLA_CON ");          

            //Tratamento de filtros opcionais:
            if (parameter.CodigoResumido.HasValue)
            {
                sql.Append("WHERE COD_RESU_CON = :Codigo ");
                jaExisteFiltroInformado = true;
            }
            if (parameter.Digito.HasValue)
            {
                if (jaExisteFiltroInformado)
                    sql.Append("AND DIG_CON = :Digito ");
                else
                {
                    sql.Append("WHERE DIG_CON = :Digito ");
                    jaExisteFiltroInformado = true;
                }
            }
            if (!String.IsNullOrEmpty(parameter.NumeroConta))
            {
                if (jaExisteFiltroInformado)
                    sql.Append("AND COD_CCTB_FORT LIKE :NumeroConta ");
                else
                {
                    sql.Append("WHERE COD_CCTB_FORT LIKE :NumeroConta ");
                    jaExisteFiltroInformado = true;
                }
            }
            if (!String.IsNullOrEmpty(parameter.NomeConta))
            {
                if (jaExisteFiltroInformado)
                    sql.Append("AND NOME_CON LIKE :NomeConta ");
                else                
                    sql.Append("WHERE NOME_CON LIKE :NomeConta ");
            }

            sql.Append("ORDER BY COD_CCTB_FORT");

            DbCommand dbCommand = this.DataBase.GetSqlStringCommand(sql.ToString());

            #endregion

            #region Adição dos parâmetros

            if (parameter.CodigoResumido.HasValue)
                this.DataBase.AddInParameter(dbCommand, "Codigo", DbType.Int32, parameter.CodigoResumido);
            if (parameter.Digito.HasValue)
                this.DataBase.AddInParameter(dbCommand, "Digito", DbType.Int32, parameter.Digito);
            if (!String.IsNullOrEmpty(parameter.NumeroConta))
                this.DataBase.AddInParameter(dbCommand, "NumeroConta", DbType.String, parameter.NumeroConta);
            if (!String.IsNullOrEmpty(parameter.NomeConta))
                this.DataBase.AddInParameter(dbCommand, "NomeConta", DbType.String, parameter.NomeConta);            

            #endregion

            #region Execução

            List<BusinessEntity.ContaContabil> response = new List<BusinessEntity.ContaContabil>();
            BusinessEntity.ContaContabil responseItem;

            using (IDataReader reader = this.DataBase.ExecuteReader(dbCommand))
            {
                while (reader.Read())
                {
                    responseItem = new BusinessEntity.ContaContabil();

                    responseItem.CodigoResumido = reader.GetValue<Int32>("COD_RESU_CON");
                    responseItem.Digito = reader.GetValue<Int32>("DIG_CON");
                    responseItem.NumeroConta = reader.GetValue<String>("COD_CCTB_FORT");
                    responseItem.NomeConta = reader.GetValue<String>("NOME_CON");                   

                    response.Add(responseItem);
                }
            }

            return response;

            #endregion
        }


        /// <summary>
        /// Consultar Históricos Contabeis - Select
        /// FGualberto.7COMm - Task: 6312
        /// </summary>
        /// <param name="request">Filtro Contabil Histórico</param>
        /// <returns>BusinessEntity.HistoricoContabil</returns>
        public List<BusinessEntity.HistoricoContabil> ConsultarHistoricosContabeis(BusinessEntity.Parameter.FiltroHistoricoContabil request)
        {
            List<BusinessEntity.HistoricoContabil> listaHistoricoContabil = null;
            bool possuiCondicao = false;
            StringBuilder sql = new StringBuilder();

            using (DbCommand command = this.DataBase.GetSqlStringCommand(" "))
            {

                sql.Append("SELECT COD_HIST Codigo, ");
                sql.Append(" DIG_HIST Digito, ");
                sql.Append(" TEXT_HIST Descricao ");
                sql.Append(" FROM TCTCAD_HIS ");

                if (request.Codigo.HasValue)
                {
                    sql.AppendLine(" WHERE COD_HIST = :CODIGO_HIST ");
                    possuiCondicao = true;
                }

                if (request.Digito.HasValue)
                {
                    if (possuiCondicao)
                    {
                        sql.AppendLine(" AND DIG_HIST = :DIGITO ");
                    }
                    else
                    {
                        sql.AppendLine(" WHERE DIG_HIST = :DIGITO ");
                        possuiCondicao = true;
                    }
                }

                if (!String.IsNullOrEmpty(request.Descricao))
                {
                    if (possuiCondicao)
                    {
                        sql.AppendLine(" AND TEXT_HIST like :HISTORICO ");
                    }
                    else
                    {
                        sql.AppendLine(" WHERE TEXT_HIST like :HISTORICO ");
                    }
                }

                sql.Append(" ORDER BY TEXT_HIST ");

                if (request.Codigo.HasValue)
                {
                    this.DataBase.AddInParameter(command, "CODIGO_HIST", DbType.Int32, request.Codigo.Value);
                }

                if (request.Digito.HasValue)
                {
                    this.DataBase.AddInParameter(command, "DIGITO", DbType.Int32, request.Digito.Value);
                }

                if (!string.IsNullOrEmpty(request.Descricao))
                {
                    this.DataBase.AddInParameter(command, "HISTORICO", DbType.String, "%" + request.Descricao + "%");
                }

                command.CommandText = sql.ToString();

                listaHistoricoContabil = this.FindAll<BusinessEntity.HistoricoContabil>(command,
                (
                    reader => new BusinessEntity.HistoricoContabil
                    {
                        Codigo = reader.GetValue<int>("Codigo"),
                        Digito = reader.GetValue<int>("Digito"),
                        Descricao = reader["Descricao"] != DBNull.Value ? reader.GetValue<string>("Descricao") : string.Empty
                    }
                ));
            }
            return listaHistoricoContabil;
        }


        #endregion
    }
}